﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using HT.Model;

namespace HT.Persistency
{
    public class ReimbursementPersistency
    {        
        public static string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\work\\HTWorkflow\\DB\\HTWorkflowDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
        private SqlConnection connection = null;
        private SqlCommand sqlCommand = null;

        /// <summary>
        /// 创建ReimbursementInfo记录
        /// </summary>
        public void insertReimbursementInfo(ReimbursementInfo reimbursementInfo)
        {
            openConnect();
            string sqlStr = "INSERT INTO [Reimbursement] ([InstanceID],[Status],[Owner],[Account],[Reason],[CreateDate],[NextHandlerID]) " +
            "VALUES (@InstanceID,@Status,@Owner,@Account,@Reason,@CreateDate,@NextHandlerID)";
            sqlCommand.CommandText = sqlStr;
            sqlCommand.Parameters.Add("@InstanceID", SqlDbType.UniqueIdentifier);
            sqlCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters.Add("@Account", SqlDbType.Real);
            sqlCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024);
            sqlCommand.Parameters.Add("@CreateDate", SqlDbType.DateTime);
            sqlCommand.Parameters.Add("@NextHandlerID", SqlDbType.NVarChar, 128);

            sqlCommand.Parameters["@InstanceID"].Value = reimbursementInfo.InstanceID;
            sqlCommand.Parameters["@Status"].Value = reimbursementInfo.Status;
            sqlCommand.Parameters["@Owner"].Value = reimbursementInfo.Owner;
            sqlCommand.Parameters["@Account"].Value = reimbursementInfo.Account;
            sqlCommand.Parameters["@Reason"].Value = reimbursementInfo.Reason;
            sqlCommand.Parameters["@CreateDate"].Value = reimbursementInfo.CreateDate;
            sqlCommand.Parameters["@NextHandlerID"].Value = reimbursementInfo.NextHandler;
            
            int x =sqlCommand.ExecuteNonQuery();
            closeConnect();
        }

        /// <summary>
        /// 更新ReimbursementInfo记录
        /// </summary>
        public void updateReimbursementInfo(ReimbursementInfo reimbursementInfo)
        {
            openConnect();
            sqlCommand.CommandText = "UPDATE [Reimbursement] SET [Status] = @Status, [Owner] = @Owner ,[Account] = @Account,[Reason] = @Reason," +
            "[RMSuggestion]=@RMSuggestion, [ACSuggestion] = @ACSuggestion, [VPSuggestion] = @VPSuggestion," +
            "[PRSuggestion] = @PRSuggestion, [NextHandlerID] = @NextHandlerID WHERE [InstanceID] = @InstanceID";
            sqlCommand.Parameters.Add("@InstanceID", SqlDbType.UniqueIdentifier);
            sqlCommand.Parameters.Add("@Status", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters.Add("@Account", SqlDbType.Real);
            sqlCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024);            
            sqlCommand.Parameters.Add("@RMSuggestion", SqlDbType.NVarChar, 1024);
            sqlCommand.Parameters.Add("@ACSuggestion", SqlDbType.NVarChar, 1024);
            sqlCommand.Parameters.Add("@VPSuggestion", SqlDbType.NVarChar, 1024);
            sqlCommand.Parameters.Add("@PRSuggestion", SqlDbType.NVarChar, 1024);
            sqlCommand.Parameters.Add("@NextHandlerID", SqlDbType.NVarChar, 128);

            sqlCommand.Parameters["@InstanceID"].Value = reimbursementInfo.InstanceID;
            sqlCommand.Parameters["@Status"].Value = reimbursementInfo.Status;
            sqlCommand.Parameters["@Owner"].Value = reimbursementInfo.Owner;
            sqlCommand.Parameters["@Account"].Value = reimbursementInfo.Account;
            sqlCommand.Parameters["@Reason"].Value = reimbursementInfo.Reason;
            sqlCommand.Parameters["@NextHandlerID"].Value = reimbursementInfo.NextHandler;            

            if (reimbursementInfo.RMSuggestion == null)                
            {
                sqlCommand.Parameters["@RMSuggestion"].Value = "未填写";
            }
            else
            {
                sqlCommand.Parameters["@RMSuggestion"].Value = reimbursementInfo.RMSuggestion;
            }

            if (reimbursementInfo.ACSuggestion == null)
            {
                sqlCommand.Parameters["@ACSuggestion"].Value = "未填写";
            }
            else 
            {
                sqlCommand.Parameters["@ACSuggestion"].Value = reimbursementInfo.ACSuggestion; 
            }

            if (reimbursementInfo.VPSuggestion == null)
            {
                sqlCommand.Parameters["@VPSuggestion"].Value = "未填写";
            }
            else
            {
                sqlCommand.Parameters["@VPSuggestion"].Value = reimbursementInfo.VPSuggestion; 
            }

            if (reimbursementInfo.PRSuggestion == null)
            {
                sqlCommand.Parameters["@PRSuggestion"].Value = "未填写";
            }
            else
            {
                sqlCommand.Parameters["@PRSuggestion"].Value = reimbursementInfo.PRSuggestion; 
            }

       
            sqlCommand.ExecuteNonQuery();
            closeConnect();
        }


        /// <summary>
        /// 根据用户Id获得用户发起的工作流列表
        /// </summary>     
        public List<ReimbursementInfo> GetReimbursementInfoListByOwner(string userId)
        {
            List<ReimbursementInfo> reimbursementInfoList = new List<ReimbursementInfo>();
            openConnect();
            sqlCommand.CommandText = "select * from Reimbursement where Owner=@Owner";
            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters["@Owner"].Value = userId;
            SqlDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ReimbursementInfo reimbursementInfo = new ReimbursementInfo();
                reimbursementInfo.InstanceID = new Guid(dataReader["InstanceID"].ToString());
                reimbursementInfo.Status = dataReader["Status"].ToString();
                reimbursementInfo.Owner = dataReader["Owner"].ToString();
                reimbursementInfo.Account = double.Parse(dataReader["Account"].ToString());
                reimbursementInfo.Reason = dataReader["Reason"].ToString();
                reimbursementInfo.CreateDate = DateTime.Parse(dataReader["CreateDate"].ToString());
                reimbursementInfo.RMSuggestion = dataReader["RMSuggestion"].ToString();
                reimbursementInfo.ACSuggestion = dataReader["ACSuggestion"].ToString();
                reimbursementInfo.VPSuggestion = dataReader["VPSuggestion"].ToString();
                reimbursementInfo.PRSuggestion = dataReader["PRSuggestion"].ToString();
                reimbursementInfo.NextHandler = dataReader["NextHandlerID"].ToString();

                reimbursementInfoList.Add(reimbursementInfo);
            }

            closeConnect();

            return reimbursementInfoList;
        }
       
        /// <summary>
        /// 根据实例ID得到流程数据
        /// </summary>
        public ReimbursementInfo GetReimbursementInfoByInstanceID(Guid instanceID)
        {
            openConnect();
            sqlCommand.CommandText = "select * from Reimbursement where InstanceID=@InstanceID";
            sqlCommand.Parameters.Add("@InstanceID", SqlDbType.UniqueIdentifier);
            sqlCommand.Parameters["@InstanceID"].Value = instanceID;
            SqlDataReader dataReader = sqlCommand.ExecuteReader();
            ReimbursementInfo reimbursementInfo = null;
            while (dataReader.Read())
            {
                reimbursementInfo = new ReimbursementInfo();
                reimbursementInfo.InstanceID = new Guid(dataReader["InstanceID"].ToString());
                reimbursementInfo.Status = dataReader["Status"].ToString();
                reimbursementInfo.Owner = dataReader["Owner"].ToString();
                reimbursementInfo.Account = double.Parse(dataReader["Account"].ToString());
                reimbursementInfo.Reason = dataReader["Reason"].ToString();
                reimbursementInfo.CreateDate = DateTime.Parse(dataReader["CreateDate"].ToString());
                reimbursementInfo.RMSuggestion = dataReader["RMSuggestion"].ToString();
                reimbursementInfo.ACSuggestion = dataReader["ACSuggestion"].ToString();
                reimbursementInfo.VPSuggestion = dataReader["VPSuggestion"].ToString();
                reimbursementInfo.PRSuggestion = dataReader["PRSuggestion"].ToString();
                reimbursementInfo.NextHandler = dataReader["NextHandlerID"].ToString();        
            }

            closeConnect();

            return reimbursementInfo;
        }

        /// <summary>
        /// 获得等待指定用户处理的工作流列表
        /// </summary>
        public List<ReimbursementInfo> GetWaitingReimbursementInfoListByUser(string userId)
        {
            List<ReimbursementInfo> reimbursementInfoList = new List<ReimbursementInfo>();
            openConnect();
            sqlCommand.CommandText = "select * from Reimbursement where NextHandlerID=@NextHandlerID";
            sqlCommand.Parameters.Add("@NextHandlerID", SqlDbType.NVarChar, 128);
            sqlCommand.Parameters["@NextHandlerID"].Value = userId;
            SqlDataReader dataReader = sqlCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ReimbursementInfo reimbursementInfo = new ReimbursementInfo();
                reimbursementInfo.InstanceID = new Guid(dataReader["InstanceID"].ToString());
                reimbursementInfo.Status = dataReader["Status"].ToString();
                reimbursementInfo.Owner = dataReader["Owner"].ToString();
                reimbursementInfo.Account = double.Parse(dataReader["Account"].ToString());
                reimbursementInfo.Reason = dataReader["Reason"].ToString();
                reimbursementInfo.CreateDate = DateTime.Parse(dataReader["CreateDate"].ToString());
                reimbursementInfo.RMSuggestion = dataReader["RMSuggestion"].ToString();
                reimbursementInfo.ACSuggestion = dataReader["ACSuggestion"].ToString();
                reimbursementInfo.VPSuggestion = dataReader["VPSuggestion"].ToString();
                reimbursementInfo.PRSuggestion = dataReader["PRSuggestion"].ToString();
                reimbursementInfo.NextHandler = dataReader["NextHandlerID"].ToString();

                reimbursementInfoList.Add(reimbursementInfo);
            }

            closeConnect();

            return reimbursementInfoList;
 
        }

        private void openConnect()
        {
            if (connection == null) connection = new SqlConnection(connectionString);
            if (connection.State != ConnectionState.Closed) connection.Close();
            connection.Open();
            sqlCommand = connection.CreateCommand();
        }

        private void closeConnect()
        {
            connection.Close();
        }
    }
}
